/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package formularios;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

//import java.sql.DriverManager;
/**
 *
 * @author lunovox
 */
public class SocketHSQL {

    //String PastaDB = "/home/lunovox/Desenvolvimento/Java/HSQL/DataBaseTeste1/";
    Connection conn = null; // conexão
    Statement st = null;
    ResultSet rs = null;

    /** Abre conexão com o banco ao instanciar-se um objeto do tipo Conexao */
    public SocketHSQL(String Onde){
        try {
            // carrega banco de dados
            conn = DriverManager.getConnection(
                "jdbc:hsqldb:file:"+Onde, // filenames
                "sa", // username
                "" //senha
            ); // password
            st = conn.createStatement();
            System.out.println("Abrindo conexão HSQL em ("+Onde+")...");
        } catch (SQLException ex) {
            System.out.println("ERRO.SocketHSQL()("+SocketHSQL.class.getName()+"): "+ex.getMessage());
            //Logger.getLogger(SocketHSQL.class.getName()).log(Level.SEVERE, null, ex);
            
        }
    } // construtor Conexao
    public boolean CriarDB_Exemplo(){
        try {
            st.executeQuery(
                "CREATE TABLE Agenda ("+
                    //"ID int(15) NOT NULL IDENTITY, "+
                    //"ID int IDENTITY(START WITH 1, INCREMENT BY 1) not  null, "+
                    "ID int GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) not  null, "+ //Ok Funciona!!!
                    "Nome varchar(255), "+
                    "Endereco varchar(255), "+
                    "Fones varchar(255),"+
                    "constraint PID primary key (ID)"+ //Ok Funciona!!!
                "); "
            ); // executa Updates
            System.out.println("Criando DB... Ok!");
            return true;
        } catch (SQLException ex) {
            //System.out.println("ERRO.CriarDB()("+SocketHSQL.class.getName()+"): "+ex.getMessage());
            //Logger.getLogger(SocketHSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    public boolean CriarDB(){
        try {
            st.executeQuery(
                //"CREATE TABLE IF NOT EXISTS TFD ("+
                "CREATE TABLE TFD ("+
                    //"ID int(15) NOT NULL IDENTITY, "+
                    //"ID int IDENTITY(START WITH 1, INCREMENT BY 1) not  null, "+
                    "ID int GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) not  null, "+ //Ok Funciona!!!
                    "Update timestamp default CURRENT_TIMESTAMP, "+ //Ok Funciona
                    //"Update datetime default CURRENT_TIMESTAMP, "+ //Ok Funciona
                        
                    "PacNome varchar(255), "+
                    "PacCNS varchar(15), "+   
                    "PacRG varchar(20), "+
                    "PacNascimento varchar(10), "+
                    "PacEndereco varchar(255), "+
                    "PacFones varchar(255),"+
                        
                    "AcoNome varchar(255), "+
                    "AcoCNS varchar(15), "+   
                    "AcoRG varchar(20), "+
                    "AcoNascimento varchar(10), "+
                    "AcoEndereco varchar(255), "+
                    "AcoFones varchar(255),"+
                        
                    "TratLocal varchar(255),"+
                    "TratCID varchar(5),"+
                    "TratNome varchar(255),"+/**/
                        
                    "constraint PID primary key (ID)"+ //Ok Funciona!!!
                "); "
            ); // executa Updates/**/
            System.out.println("Criando DB... Ok!");
            return true;
        } catch (SQLException ex) {
            //System.out.println("ERRO.CriarDB()("+SocketHSQL.class.getName()+"): "+ex.getMessage());
            //Logger.getLogger(SocketHSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    public boolean IncrementarDB_exemplo(){
        return IncrementarDB(
            "Lunovox","11987654321","6565656","28/01/1982","Aqui","(81)8731-0165",
            "C.Leda", "11987654321","6565656","25/12/1512","Aqui","(81)8765-4321",
            "aaaa","aaaa","aaaa"
        );/**/
    }
    public boolean IncrementarDB(
        String strPacNome, String strPacCNS, String strPacRG, String strPacNascimento, String strPacEndereco, String strPacFones, 
        String strAcoNome, String strAcoCNS, String strAcoRG, String strAcoNascimento, String strAcoEndereco, String strAcoFones,
        String strTratLocal, String strTratCID, String strTratNome
    ){
        try {
            st.executeQuery(
                "INSERT INTO TFD ("+
                    "PacNome, PacCNS, PacRG, PacNascimento, PacEndereco, PacFones, " + 
                    "AcoNome, AcoCNS, AcoRG, AcoNascimento, AcoEndereco, AcoFones, "+
                    "TratLocal, TratCID, TratNome "+
                ") VALUES ("+
                    "'"+strPacNome+"', '"+strPacCNS+"', '"+strPacRG+"', '"+strPacNascimento+"', '"+strPacEndereco+"', '"+strPacFones+"', "+
                    "'"+strAcoNome+"', '"+strAcoCNS+"', '"+strAcoRG+"', '"+strAcoNascimento+"', '"+strAcoEndereco+"', '"+strAcoFones+"', "+
                    "'"+strTratLocal+"', '"+strTratCID+"', '"+strTratNome+"' "+ 
                ");");
            System.out.println("Incrementando DB... Ok!");
            return true;
        } catch (SQLException ex) {
            System.out.println("ERRO.IncrementarDB()("+SocketHSQL.class.getName()+"): "+ex.getMessage());
            Logger.getLogger(SocketHSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    public boolean ConsultarDB_Exemplo(){
        try {
            System.out.println("Consultando DB...");
            System.out.println(" > --------------------------------------------");
            rs = st.executeQuery("SELECT * FROM Agenda");
            while(rs.next()){
                Integer ID = rs.getInt("ID");
                String Nome = rs.getString("Nome");
                String Endereco = rs.getString("Endereco");
                String Fones = rs.getString("Fones");
                //rs.getTimestamp(Fones)

                System.out.println(" > ID......: "+ID);
                System.out.println(" > Nome....: "+Nome);
                System.out.println(" > Endereco: "+Endereco);
                System.out.println(" > Fones...: "+Fones);
                System.out.println(" > --------------------------------------------");
            }
            return true;
        } catch (SQLException ex) {
            System.out.println("ERRO.ConsultarDB()("+SocketHSQL.class.getName()+"): "+ex.getMessage());
            Logger.getLogger(SocketHSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    public ResultSet ConsultarDB(Integer intID){
        try {
            System.out.println("Consultando DB...");
            rs = st.executeQuery("SELECT * FROM TFD WHERE ID LIKE '"+intID.toString()+"'");
            return rs;
        } catch (SQLException ex) {
            System.out.println("ERRO.ConsultarDB()("+SocketHSQL.class.getName()+"): "+ex.getMessage());
            Logger.getLogger(SocketHSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    public boolean FecharDBeConexao(){
        try {
            System.out.println("Fechando conexão HSQL...");
            st.execute("SHUTDOWN");
            st.close();
            conn.close(); // encerraConexão
            return true;
        } catch (SQLException ex) {
            //System.out.println("ERRO.FecharDBeConexao()("+SocketHSQL.class.getName()+"): "+ex.getMessage());
            //Logger.getLogger(SocketHSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    
/**
 * @param args the command line arguments
 */
    /*public static void main(String[] args){
        String strPastaBD = System.getProperty("user.dir");
        String strbar = System.getProperty("file.separator");
        //SocketHSQL myHSQL = new SocketHSQL("~/Área de Trabalho/HSQL_-_Teste_de_DB01/");
        SocketHSQL myHSQL = new SocketHSQL("~/Área de Trabalho/HSQL_-_Teste_de_DB01/");
        //SocketHSQL myHSQL = new SocketHSQL(strPastaBD+strbar+"HSQL_-_Teste_de_DB01/");
        if(myHSQL.CriarDB()){
            //myHSQL.IncrementarDB();
        }
        //testeHSQL.IncrementarDB();
        //myHSQL.ConsultarDB();
        myHSQL.FecharDBeConexao();
    }/**/
}
